package com.jiguang.shangcheng.dao;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.jiguang.shangcheng.pojo.Order;
import com.jiguang.shangcheng.pojo.Product;
import com.jiguang.shangcheng.pojo.User;
import com.jiguang.shangcheng.utils.MD5;
import com.sun.org.apache.xpath.internal.operations.Or;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class AdminDao {

    static Connection conn = BaseDao.getconn();
    //后台登录
    @SneakyThrows
    public boolean adminLogin(String number, String password) throws SQLException {
        if(conn != null){
            conn.close();
            conn = BaseDao.getconn();
        }
        //密码加密
        String pwd = MD5.md5(password);

        //判断账号密码是否正确
        String adminLogin_sql = "select count(*) from user where number= ? and password = ? and admin = ?";
        PreparedStatement pst = conn.prepareStatement(adminLogin_sql);
        pst.setString(1,number);
        pst.setString(2,pwd);
        pst.setString(3,"1");

        ResultSet resultSet = pst.executeQuery();

        int count = 0;
        while (resultSet.next()){
            count = resultSet.getInt(1);
        }
        if(count>0) return true;

        return false;
    }

    //查询用户信息
    public User selectUser(String number, String password) throws Exception {
        User user = new User();
        //密码加密
        String pwd = MD5.md5(password);

        //查询数据库
        String selectUser_sql = "select * from user where number = ? and password = ?";
        PreparedStatement pst = conn.prepareStatement(selectUser_sql);
        pst.setString(1,number);
        pst.setString(2,pwd);
        ResultSet rs = pst.executeQuery();

        while (rs.next()){
            user = new User(
                    rs.getInt("id"),
                    rs.getString("number"),
                    rs.getString("netname"),
                    rs.getString("password"),
                    rs.getString("turename"),
                    rs.getString("bank_card"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("admin")
            );
        }
        return user;
    }

    //通过用户Id查询用户信息
    public User selectUserByid(Integer id) throws Exception {
        User user = new User();

        //查询数据库
        String selectUser_sql = "select * from user where id = ?";
        PreparedStatement pst = conn.prepareStatement(selectUser_sql);
        pst.setInt(1,id);
        ResultSet rs = pst.executeQuery();

        while (rs.next()){
            user = new User(
                    rs.getInt("id"),
                    rs.getString("number"),
                    rs.getString("netname"),
                    rs.getString("password"),
                    rs.getString("turename"),
                    rs.getString("bank_card"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("admin")
            );
        }

        return user;
    }

    //分页查询所有数据
    public List<User> selectAllUser(int page, int count) throws Exception {
        List<User> list = new ArrayList<>();


        //分析查询
        String sellectAllUser_sql = "select * from user where state !=? limit ?,?";
        PreparedStatement pst = conn.prepareStatement(sellectAllUser_sql);
        pst.setString(1,"2");
        pst.setInt(2,(page-1)*count);//从哪里开始
        pst.setInt(3,count);//查询多少个

        ResultSet rs = pst.executeQuery();

        //获取用户信息
        while (rs.next()){
            User user = new User(
                    rs.getInt("id"),
                    rs.getString("number"),
                    rs.getString("netname"),
                    rs.getString("password"),
                    rs.getString("turename"),
                    rs.getString("bank_card"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("admin")
            );
            //将数据加入集合
            list.add(user);
        }
        return list;
    }

    //查询总数据以及总页数
    public int[] selectPage(int count) throws Exception {
        //0总数据，1总页数
        int sum[] = {0,1};
        //查询数据
        String selectPage_sql = "select count(*) from user where state != ?";
        PreparedStatement pst = conn.prepareStatement(selectPage_sql);
        pst.setString(1,"2");
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            sum[0] = rs.getInt(1);
        }

        if (sum[0]%count==0){
            sum[1] = sum[0]/count;
        }else {
            sum[1] = sum[0]/count+1;
        }

        return sum;
    }

    //删除用户
    public static boolean deleteUser(Integer id) throws Exception {
        //进行假删除，将账号状态设为2

        String deleteUser_sql = "update user set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"2");
        ps.setInt(2,id);
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }

    //设置为管理员
    public static boolean setAmdin(int id) throws Exception {
        //进行假删除，将账号状态设为2

        String deleteUser_sql = "update user set admin = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"1");
        ps.setInt(2,id);
        int update = ps.executeUpdate();
        System.out.println(update);

        if(update>0) return true;
        return false;
    }

    //取消为管理员
    public static boolean setAmdin1(int id) throws Exception {
        //进行假删除，将账号状态设为2

        String deleteUser_sql = "update user set admin = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"0");
        ps.setInt(2,id);
        int update = ps.executeUpdate();
        System.out.println(update);
        if(update>0) return true;
        return false;
    }

    //删除订单
    public static boolean deleteOrder(int id) throws Exception {
        //进行假删除，将账号状态设为4

        String deleteUser_sql = "update userorder set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"4");
        ps.setInt(2,id);
        int update = ps.executeUpdate();
        if(update>0) return true;

        return false;
    }

    //删除商品
    public static boolean deleteProduct(int id) throws Exception {
        //进行假删除，将账号状态设为3

        String deleteUser_sql = "update product set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"2");
        ps.setInt(2,id);
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }

    //下架商品
    public static boolean allowProduct(int id) throws Exception {

        String deleteUser_sql = "update product set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"1");
        ps.setInt(2,id);
        int update = ps.executeUpdate();

        if(update>0) return true;
        return false;
    }

    //上架商品
    public static boolean downProduct(int id) throws Exception {
        //获取当前时间
        Date date = new Date();
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.HOUR_OF_DAY,8);
        date = c.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        String deleteUser_sql = "update product set state = ?, create_time=? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"0");
        ps.setString(2,sdf.format(date));
        ps.setInt(3,id);
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }

    //通过注册
    public static boolean allowUser(int id) throws Exception {
        //将账号状态设为0

        String deleteUser_sql = "update user set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"0");
        ps.setInt(2,id);
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }

    //封号
    public static boolean closewUser(int id) throws Exception {
        //将账号状态设为1

        String deleteUser_sql = "update user set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,"1");
        ps.setInt(2,id);
        int update = ps.executeUpdate();

        if(update>0) return true;
        return false;
    }

    //修改数据
    public boolean updateUser(User user) throws Exception {


        String sql = "update user set number=? ,netname=? ,password=? ,turename=? ,bank_card=? ,photo=? where id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,user.getNumber());
        ps.setString(2,user.getNetname());
        ps.setString(3,user.getPassword());
        ps.setString(4,user.getTurename());
        ps.setString(5,user.getBankCard());
        ps.setString(6,user.getPhoto());
        ps.setInt(7,user.getId());
        int i = ps.executeUpdate();
        if(i>0) return true;

        return false;
    }

    //分页查询所有商品数据
    public List<Product> selectAllProduct(int page, int count) throws Exception {
        List<Product> list = new ArrayList<>();

        //分析查询
        String sellectAllUser_sql = "select * from product where state != ? limit ?,?";
        PreparedStatement pst = conn.prepareStatement(sellectAllUser_sql);
        pst.setString(1,"2");
        pst.setInt(2,(page-1)*count);//从哪里开始
        pst.setInt(3,count);//查询多少个

        ResultSet rs = pst.executeQuery();

        //获取用户信息
        while (rs.next()){
            Product product = new Product(
                    rs.getInt("id"),
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("price"),
                    rs.getString("game"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("create_time")
            );
            //将数据加入集合
            list.add(product);
        }
        return list;
    }

    //通过商品ID查询商品信息
    public Product selectProductByid(int id) throws Exception {
        Product product = new Product();

        //分析查询
        String sellectAllUser_sql = "select * from product where id = ?";
        PreparedStatement pst = conn.prepareStatement(sellectAllUser_sql);
        pst.setInt(1,id);

        ResultSet rs = pst.executeQuery();

        //获取用户信息
        while (rs.next()){
            product = new Product(
                    rs.getInt("id"),
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("price"),
                    rs.getString("game"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("create_time")
            );
        }
        return product;
    }

    //根据ID查询订单信息
    public Order selectOrderByid(int id) throws Exception {
        Order order = new Order();

        //分析查询
        String sellectAllUser_sql = "select * from userorder where id = ?";
        PreparedStatement pst = conn.prepareStatement(sellectAllUser_sql);
        pst.setInt(1,id);

        ResultSet rs = pst.executeQuery();

        //获取用户信息
        while (rs.next()){
            order = new Order(
                    rs.getInt("id"),
                    rs.getInt("user_id"),
                    rs.getInt("product_id"),
                    rs.getString("state"),
                    rs.getString("create_time"),
                    rs.getString("paid")
            );
        }
        return order;
    }

    //查询商品总数以及总页数
    public int[] selectProductPage(int count) throws Exception {
        //0总数据，1总页数
        int sum[] = {0,1};

        //查询数据
        String selectPage_sql = "select count(*) from product where state != ?";
        PreparedStatement pst = conn.prepareStatement(selectPage_sql);
        pst.setString(1,"2");
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            sum[0] = rs.getInt(1);
        }

        if (sum[0]%count==0){
            sum[1] = sum[0]/count;
        }else {
            sum[1] = sum[0]/count+1;
        }
        return sum;
    }

    //修改数据
    public boolean updateProduct(Product product) throws Exception {

        String sql = "update product set name=? ,price=? ,game=? ,photo=? ,create_time=? where id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,product.getName());
        ps.setString(2,product.getPrice());
        ps.setString(3, product.getGame());
        ps.setString(4,product.getPhoto());
        ps.setString(5,product.getCreateTime());
        ps.setInt(6,product.getId());
        int i = ps.executeUpdate();

        if(i>0) return true;
        return false;
    }

    //查询所有订单
    public List<Order> selectAllOrder(int page, int count) throws Exception {
        List<Order> orderList = new ArrayList<>();

        //查询数据库
        //分析查询
        String sellectAllUser_sql = "select * from userorder where state != ? limit ?,?";
        PreparedStatement pst = conn.prepareStatement(sellectAllUser_sql);
        pst.setString(1,"4");
        pst.setInt(2,(page-1)*count);//从哪里开始
        pst.setInt(3,count);//查询多少个

        ResultSet rs = pst.executeQuery();

        while (rs.next()){
            Order order = new Order(
                    rs.getInt("id"),
                    rs.getInt("user_id"),
                    rs.getInt("product_id"),
                    rs.getString("state"),
                    rs.getString("create_time"),
                    rs.getString("paid")
            );
            orderList.add(order);
        }

        return orderList;
    }

    //查询订单总数以及总页数
    public int[] selectOrderPage(int count) throws Exception {
        //0总数据，1总页数
        int sum[] = {0,1};

        //查询数据
        String selectPage_sql = "select count(*) from userorder where state != ?";
        PreparedStatement pst = conn.prepareStatement(selectPage_sql);
        pst.setString(1,"4");
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            sum[0] = rs.getInt(1);
        }

        if (sum[0]%count==0){
            sum[1] = sum[0]/count;
        }else {
            sum[1] = sum[0]/count+1;
        }
        return sum;
    }

    //修改订单信息
    public boolean updateOrder(String createTime,Integer id) throws Exception {

        String sql = "update userorder set create_time=? where id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,createTime);
        ps.setInt(2,id);
        int i = ps.executeUpdate();

        if(i>0) return true;
        return false;
    }

    //修改卖家名字
    public void updateUserNetname(Integer id,String name) throws SQLException {
        String sql = "update user set netname=? where id = ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,name);
        ps.setInt(2,id);
        int i = ps.executeUpdate();
        return;
    }
}
